<?php
include('db_connection.php');

class affUser {

    
    function export_csv($postedData = ''){
        $condition_search = '';
        $value = '';
        if (isset($postedData['search_value'])) {
            $value = trim($postedData['search_value']);
            if (!empty($value)) {
                $cond_where = 'WHERE ';
                $condition_search = $cond_where . " (username like '%" . $value . "%' OR email_id like '%" . $value . "%' OR phone like '%" . $value . "%' OR city like '%" . $value . "%' OR country like '%" . $value . "%')";
            }
        }
       $query = "SELECT * ,af.id AS affiliate_id,SUM(c.`commission`) as total_commission FROM tbl_affiliate AS af 
JOIN tbl_user AS u ON af.id = REPLACE(u.reffered_by,'a_','')
JOIN tbl_commission AS c ON c.`trainee_id` = u.`id`
JOIN tbl_user_details AS ud ON c.`trainee_detail_id` = ud.`id` 
LEFT JOIN tbl_bank_detail AS bk ON bk.b_affilate_id = af.id 
WHERE ud.`is_refund` = '0' AND u.`is_active` =  1  AND u.`is_paid` = 1 " . $condition_search . "
GROUP BY af.id
ORDER BY af.id DESC" ;
        $data = mysql_query($query)or die(mysql_error());
		
		$filename = 'csv/Affiliate.csv';
		$output = fopen($filename, 'w');

        fputcsv($output, array('UserName','Phone No.','Email','Address','City','Country','Web Url','Date', 'Total Amount','Paid Amount','Remaining Amount','Payment Type','Bank Name','Account No.','Branch','Pancard','Paypal Name','Active'));
        $total_amount = '0';
        $pending_amount = '0';
        $paid_amount = 0;
        while($rows = mysql_fetch_array($data)){
           $select_paidamount = "SELECT SUM(total_amount) AS total_amount FROM tbl_aff_transfer  WHERE at_affilate_id= '" . $rows['affiliate_id'] . "'";
            $query_paidamount = mysql_query($select_paidamount) or die(mysql_error());
            $total_paidamount = mysql_fetch_assoc($query_paidamount);
            $paid_amount = $total_paidamount['total_amount'];
            $pending_amount = $rows['total_commission'] - $total_paidamount['total_amount'];
           // $total_amount = $paid_amount + $pending_amount;
                                            
        
                                            
            $row['username'] = $rows['username'];
            $row['phone'] = $rows['phone'];
            $row['email_id'] = $rows['email_id'];
            $row['address'] = $rows['address'];
            $row['city'] = $rows['city'];
            $row['country'] = $rows['country'];
            $row['website_url'] = $rows['website_url'];
            $row['date'] = $rows['create_ts'];
            $row['total_amount'] = $rows['total_commission'];
            $row['paid_amount'] = $paid_amount;
            $row['pending_amount'] = $pending_amount;
            
            $row['b_payment_type'] = $rows['b_payment_type'];
            $row['b_bank_name'] = $rows['b_bank_name'];
            $row['b_bank_acc_no'] = $rows['b_bank_acc_no'];
            $row['b_bank_branch'] = $rows['b_bank_branch'];
            $row['b_pancard'] = $rows['b_pancard'];
            $row['b_paypal_name'] = $rows['b_paypal_name'];
            
           
            
            if($rows['is_active'] == 1){
                $row['active'] = 'Yes';
            }else{
                $row['active'] = 'No';
            }
            fputcsv($output, $row);
        }
        $filename1 = 'Affiliate.csv';
		$this->dl_file('csv/Affiliate.csv');
        exit;
                                
    }
	
	
    public static function dl_file($file) {

        //First, see if the file exists
        if (!is_file($file)) {
            die(
                    "<div style='text-align:center;'><h1> Sorry, file temporary not available!</h1></div>");
        }

        //Gather relevent info about file
        $len = filesize($file);
        $filename = basename($file);
        $file_extension = strtolower(substr(strrchr($filename, "."), 1));

        //This will set the Content-Type to the appropriate setting for the file
        switch ($file_extension) {
            case "pdf":
                $ctype = "application/pdf";
                break;
            case "exe":
                $ctype = "application/octet-stream";
                break;
            case "zip":
                $ctype = "application/zip";
                break;
            case "doc":
                $ctype = "application/msword";
                break;
            case "xls":
                $ctype = "application/vnd.ms-excel";
                break;
            case "ppt":
                $ctype = "application/vnd.ms-powerpoint";
                break;
            case "gif":
                $ctype = "image/gif";
                break;
            case "png":
                $ctype = "image/png";
                break;
            case "jpeg":
            case "jpg":
                $ctype = "image/jpg";
                break;
            case "mp3":
                $ctype = "audio/mpeg";
                break;
            case "wav":
                $ctype = "audio/x-wav";
                break;
            case "mpeg":
            case "mpg":
            case "mpe":
                $ctype = "video/mpeg";
                break;
            case "mov":
                $ctype = "video/quicktime";
                break;
            case "avi":
                $ctype = "video/x-msvideo";
                break;

            //The following are for extensions that shouldn't be downloaded (sensitive stuff, like php files)
            case "php":
            case "htm":
            case "html":
            case "txt":
                die("<b>Cannot be used for " . $file_extension . " files!</b>");
                break;

            default:
                $ctype = "application/force-download";
        }

        //Begin writing headers
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Cache-Control: public");
        header("Content-Description: File Transfer");

        //Use the switch-generated Content-Type
        header("Content-Type: $ctype");

        //Force the download
        //$header="Content-Disposition: attachment; filename=".$filename.";"; //for force fully download
        $header = "Content-Disposition: inline; filename=" . $filename . ";";
        header($header);
        header("Content-Transfer-Encoding: binary");
        header("Content-Length: " . $len);
        @readfile($file);
        exit;
    }

	
    
    function del_user($user_id) {
        $del_update = "UPDATE tbl_affiliate SET is_active = '0', end_ts = now() WHERE id='" . $user_id . "'";
        $query_update = mysql_query($del_update) or die(mysql_error());
        $success_msg = "User Has been Sucessfully Inactive";
        return $success_msg;
    }

    function activate_user($user_id) {
        $act_update = "UPDATE tbl_affiliate SET is_active = '1', end_ts = now() WHERE id='" . $user_id . "'";
        $query_update = mysql_query($act_update) or die(mysql_error());

        $select = "SELECT * FROM tbl_affiliate WHERE id='" . $user_id . "'";
        $query = mysql_query($select) or die(mysql_eror());
        $row = mysql_num_rows($query);
        $msg = "";
        if ($row > 0) {
            $useremail = '';
            $passwd = '';
            while ($row_user = mysql_fetch_array($query)) {
                $useremail = $row_user['email_id'];
                $passwd = $row_user['password'];
            }

            if ($passwd == '') {
                $pwd = $this->createRandomStringForReference();

                $act_update = "UPDATE tbl_affiliate SET password='" . md5($pwd) . "', end_ts = now() WHERE id='" . $user_id . "'";
                $query_update = mysql_query($act_update) or die(mysql_error());
            }
            /* This Code is used for send Email. Start Here */

            $username = $useremail;
            $password = $pwd;
            ob_start();
            include_once('../../mailtemplate/affiliate_login.php');
            $mail_body = ob_get_contents();
            ob_end_clean();
                        
                        
            
            $mail_to = $useremail;
            $mail_subject = "User Name And Password";
           
            $headers = 'MIME-Version: 1.0' . "\r\n";
            $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
            $headers .= 'From:RV Training<info@rvtraining.net>' . "\r\n";
            $sent = mail($mail_to, $mail_subject, $mail_body, $headers);

            $mail_to = 'Vipin7477@gmail.com';
            $mail_subject = "User Name And Password";
            $headers = 'MIME-Version: 1.0' . "\r\n";
            $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
            $headers .= 'From:RV Training<info@rvtraining.net>' . "\r\n";
            $sent = mail($mail_to, $mail_subject, $mail_body, $headers);

            /* This Code is used for send Email. End Here */
        }
        $success_msg = "User Has been Sucessfully Active";
        return $success_msg;
    }

    function createRandomStringForReference() {
        $random_string_final = '';
        $pwd_row = '';
        $alpha_arr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
        $numeric_arr = array('0', '1', '2', '3', '4', '5', '6', '7', '8', '9');


        $random_alpha_string = $this->array_random_assoc($alpha_arr, 4);
        $random_numeric_string = $this->array_random_assoc($numeric_arr, 4);
        $random_string = $random_alpha_string . $random_numeric_string;
        $random_string = str_shuffle($random_string);

        $random_string_final = $random_string;

        return $random_string_final;
    }

    function array_random_assoc($arr, $num = 1) {
        $keys = array_keys($arr);
        shuffle($keys);

        $r = '';
        for ($i = 0; $i < $num; $i++) {
            $r .= $arr[$keys[$i]];
        }
        return $r;
    }

    function get_course($id) {
        $select = "SELECT * FROM tbl_courses WHERE id='" . $id . "'";
        $query = mysql_query($select) or die(mysql_error());

        if (mysql_num_rows($query)) {
            $results = array();
            while ($row = mysql_fetch_array($query)) {
                foreach ($row as $k => $v) {
                    $result->$k = $v;
                }

                $results[] = $result;
            }
            return $results;
        }
    }

    function get_aff_user($id) {
        $select = "SELECT * FROM tbl_affiliate WHERE id='" . $id . "'";
        $query = mysql_query($select) or die(mysql_error());

        if (mysql_num_rows($query)) {
            $results = array();
            while ($row = mysql_fetch_array($query)) {
                foreach ($row as $k => $v) {
                    $result->$k = $v;
                }

                $results[] = $result;
            }
            return $results;
        }
    }

    function update_course() {
        $update = "UPDATE tbl_courses SET course_commission = '" . $_POST['course_commission'] . "',
											update_ts = now()
											WHERE id='" . $_POST['course_id'] . "'";
        $query_update = mysql_query($update) or die(mysql_error());
        $success_msg = "Course Has been Sucessfully Updated";
        return $success_msg;
    }

    function update_aff_wdrwamt() {
        $min_amt = '';
        $max_amt = '';
        if ($_POST['min_amt_wdrw'] == '' && $_POST['max_amt_wdrw'] == '') {
            $min_amt = '20';
            $max_amt = '1000';
        } else if ($_POST['min_amt_wdrw'] == '') {
            $min_amt = '20';
        } else if ($_POST['max_amt_wdrw'] == '') {
            $min_amt = '1000';
        } else {
            $min_amt = $_POST['min_amt_wdrw'];
            $max_amt = $_POST['max_amt_wdrw'];
        }

        $update_wdrwamt = "UPDATE tbl_affiliate SET is_active = '" . $_POST['is_active'] . "',
											withdraw_min_amount = '" . $_POST['min_amt_wdrw'] . "',
											withdraw_max_amount = '" . $_POST['max_amt_wdrw'] . "',
											address = '" . $_POST['aff_address'] . "',
											
											create_ts = now()
											WHERE id='" . $_POST['aff_usr_id'] . "'";
        $query_wdrwamt = mysql_query($update_wdrwamt) or die(mysql_error());
        $success_msg = "Affilate User Has been Sucessfully Updated";
        return $success_msg;
    }

    function get_details($id) {
        $select = "SELECT * FROM tbl_aff_transfer WHERE at_affilate_id='" . $id . "'";
        $query = mysql_query($select) or die(mysql_error());

        if (mysql_num_rows($query)) {
            $results = array();
            while ($row = mysql_fetch_array($query)) {
                foreach ($row as $k => $v) {
                    $result->$k = $v;
                }

                $results[] = $result;
            }
            return $results;
        }
    }

    function get_affiliateName($id) {
         
        $select = "SELECT af.username FROM tbl_user as ur
        JOIN tbl_affiliate as af ON af.id = REPLACE(ur.reffered_by,'a_','')    
        WHERE ur.id='" . $id . "'";
        $query = mysql_query($select) or die(mysql_error());

        if (mysql_num_rows($query)) {
            $results = array();
            $row = mysql_fetch_assoc($query);
            return $row['username'];
        }
    }

}

?>